from dao.BaseDao import BaseDao
from Static import Static
import pandas as pd
from util import CommonHelper


class RecordDao(BaseDao):
    def initDB(self):
        self.__createTableRecord()

    def __createTableRecord(self):
        sql = """
            CREATE TABLE IF NOT EXISTS `{}` (
                `id` int NOT NULL AUTO_INCREMENT,
                `patient_id` VARCHAR ( 20 ),
                `dept_id` int,
                `doctor_id` int,
                `first_visit` date,
                `condition` VARCHAR ( 255 ) DEFAULT '-',
                `present_illness` VARCHAR ( 255 ) DEFAULT '-',
                `disease` VARCHAR ( 4000 ) DEFAULT '[]',
            PRIMARY KEY ( `id` ) 
            ) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;""".format(
            Static.DB_Record
        )
        self.executeNoResult(sql)

    def importRecords(self, df):
        self.__createTableRecord()
        self.write2sql(
            df,
            Static.DB_Record,
        )

    def getRecords(self, patientName, deptId, doctorName):
        self.__createTableRecord()
        sql_query = """
            SELECT
                r.`dept_id`,
                r.`patient_id`,
                r.`id`,
                r.`first_visit`,
                r.`condition`,
                r.`present_illness`,
                u.`nickname` doctorName,
                d.`name` deptName,
                p.`name` patientName,
                p.`outpatient_code`,
                p.`inpatient_code`,
                p.`genderStr`,
                TIMESTAMPDIFF(YEAR, p.`birthday`, CURDATE()) AS age
            FROM
                `{}` r
                LEFT JOIN ( SELECT * FROM `{}` ) d ON d.`id` = r.dept_id
                LEFT JOIN ( SELECT pp.*, s_gender.dictLabel genderStr FROM `{}` pp LEFT JOIN ( SELECT * FROM `{}` WHERE `key` = 'sys_user_sex' ) s_gender ON s_gender.dictValue = pp.gender ) p ON p.`id` = r.patient_id
                LEFT JOIN ( SELECT * FROM `{}` ) u ON u.`id` = r.doctor_id
        """.format(
            Static.DB_Record,
            Static.DB_Department,
            Static.DB_Patient,
            Static.DB_SystemDict,
            Static.DB_User,
        )
        where_clause = []
        if patientName:
            where_clause.append(f"p.`name` LIKE '%%{patientName}%%'")
        if deptId:
            where_clause.append(f"r.dept_id = {deptId}")
        if doctorName:
            where_clause.append(f"u.`nickname` LIKE '%%{doctorName}%%'")
        if where_clause:
            sql_query += f" WHERE {' AND '.join(where_clause)}"
        df = self.readSQL(sql_query)
        return df

    def getRecordInfo(self, recordId):
        self.__createTableRecord()
        sql_query = """
            SELECT
                r.*,
                u.`nickname` doctorName,
                d.`name` deptName,
                p.`name` patientName,
                p.`outpatient_code` outpatient_code,
                p.`inpatient_code` inpatient_code,
                s_gender.dictLabel genderStr,
                p.`birthday`,
                TIMESTAMPDIFF(YEAR, p.`birthday`, CURDATE()) AS age
            FROM
                `{}` r
                LEFT JOIN ( SELECT * FROM `{}` ) d ON d.`id` = r.dept_id
                LEFT JOIN ( SELECT * FROM `{}` ) p ON p.`id` = r.patient_id
                LEFT JOIN ( SELECT * FROM `{}` ) u ON u.`id` = r.doctor_id
                LEFT JOIN ( SELECT * FROM `{}` WHERE `key` = 'sys_user_sex' ) s_gender ON s_gender.dictValue = u.gender
            WHERE
                r.`id` = {}
        """.format(
            Static.DB_Record,
            Static.DB_Department,
            Static.DB_Patient,
            Static.DB_User,
            Static.DB_SystemDict,
            recordId,
        )
        df = self.readSQL(sql_query)
        return df

    def delRecord(self, recordId):
        self.__createTableRecord()
        sql_query = """
            DELETE FROM
                `{}`
            WHERE
                `id` = {}
        """.format(
            Static.DB_Record, recordId
        )
        self.executeNoResult(sql_query)

    def updateRecord(
        self,
        recordId,
        patientId,
        deptId,
        doctorId,
        outpatientCode,
        inpatientCode,
        first_visit,
        condition,
        present_illness,
        disease,
    ):
        self.__createTableRecord()
        sql_query = """
            UPDATE
                `{}`
            SET
                `patient_id` = {},
                `dept_id` = {},
                `doctor_id` = {},
                `condition` = '{}',
                `present_illness` = '{}',
                `first_visit` = '{}',
                `disease` = '{}'
            WHERE
                `id` = {}
        """.format(
            Static.DB_Record,
            patientId,
            deptId,
            doctorId,
            condition,
            present_illness,
            first_visit,
            disease,
            recordId,
        )
        print(sql_query)
        self.executeNoResult(sql_query)
